package edu.lit.wantwords;

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;

public class InsertWords {
    public static List<String> test(File file) throws Exception{
        FileInputStream in = new FileInputStream(file);
        Map<String,List<String>> mapData = new HashMap<String, List<String>>();
        ArrayList<String> arrayList = new ArrayList<>();
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(100)  //缓存到内存中的行数，默认是10
                .bufferSize(10240)  //读取资源时，缓存到内存的字节大小，默认是1024
                .open(in);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
        int sheetNums = wk.getNumberOfSheets();
        for(int i = 0 ; i < sheetNums;i ++){
            List<String> sheetData = new ArrayList<String>();
            Sheet sheet = wk.getSheetAt(i);
            String sheetName = wk.getSheetName(i);
            //遍历所有的行
            int k = 0;
            for (Row row : sheet) {
                StringBuilder sb = new StringBuilder();
                //遍历所有的列
                for (Cell cell : row) {
                    sb.append(cell.getStringCellValue() + "<>");
                }
                arrayList.add(sb.toString());
            }
        }
        return arrayList;
    }
    private static Long id = 1000000000000000000L;
    private static String insert = "INSERT INTO words (id, word, sort_num, translation, frequency) VALUES(";
    public static void main(String[] args) throws Exception {
        File file = new File("C:\\Users\\86184\\Desktop\\image\\5500-已转档.xlsx");
        List<String> test = test(file);
        List<String[]> list = new ArrayList<>();
        for (String s : test) {
            String[] split = s.split("<>");
            list.add(split);
        }
        for (int i = 0; i < list.size(); i ++ ) {
            if ( "abandon".equals(list.get(i)[0])) {
                String[] s = list.get(0);
                list.set(0, list.get(i));
                list.set(i, s);
            }
        }
        for (int i = 0; i < list.size(); i ++ ) {
            String[] s = list.get(i);
            System.out.println(insert + "\'" +(id+i) + "\', " + "\'" + s[0] + "\'" + ", " + i + ", " + "\'" + s[1] + "\'"
            + "," + "\'" + s[2] + "\'" + ");");
        }
    }
}
